﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for CompanyReport_JasaJaringanTable
    /// </summary>
    public class CompanyReport_JasaJaringanTable : CacheAbstraction
    {
        public DataTable GetJasaJaringanRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetJasaJaringanRekap(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetJasaJaringanRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_JASA_JARINGAN_REKAP, 
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.DWLA_LA_DWH);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmStart;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmEnd;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Year;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Year;

                String sxQuery =
                    @"
                      SELECT   --dpel.company_no,
                               --dpel.company_name,
                               --dper.month_name,
                               COUNT (DISTINCT a.CONTRACT_NO) Jumlah_Jaringan,
                               --djj.network_service_type_1_code Jasa_Level_1,
                               --djj.network_service_type_2_code Jasa_Level_2,
                               djj.network_service_type_3_code Jasa_Level_3,
                               dper.year_num,
                               dper.MONTH_NUM
                        FROM   dim_period dper,
                               t_fact_saldo_akhir_target a,
                               dim_pelanggan dpel,
                               dim_organisasi_sales dos,
                               dim_network_owner dno,
                               dim_jenis_jasa djj
                       WHERE       a.network_owner_key = dno.network_owner_key
                               AND a.pelanggan_key = dpel.pelanggan_key
                               AND a.period_bulan_data_key = dper.period_key
                               AND a.organisasi_sales_key = dos.organisasi_sales_key
                               --AND dper.year_num = 2011
                               --AND dper.MONTH_NUM = 5
                               AND to_date('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= to_date(dper.DAY_NUM || '\' || dper.MONTH_NUM || '\' || dper.year_num, 'dd\mm\yyyy')
                               AND ADD_MONTHS(to_date('1\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy'), 1) > to_date(dper.DAY_NUM || '\' || dper.MONTH_NUM || '\' || dper.year_num, 'dd\mm\yyyy')
                               AND dpel.COMPANY_ID = :prmID --103893
                               AND a.jenis_jasa_key = djj.jenis_jasa_key
                    GROUP BY   --dper.year_num,
                               --dper.month_name,
                               --dpel.company_no,
                               --dpel.company_name,
                               dper.year_num,
                               dper.MONTH_NUM,
                               --djj.network_service_type_1_code,
                               --djj.network_service_type_2_code,
                               djj.network_service_type_3_code
                    ORDER BY   dper.year_num DESC, dper.MONTH_NUM DESC 
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetJasaJaringanDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetJasaJaringanDetail(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetJasaJaringanDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_JASA_JARINGAN_DETAIL,
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if(bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.DWLA_LA_DWH);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmStart;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmEnd;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Year;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Year;

                String sxQuery =
                    @"
                      SELECT   dpel.company_no,
                               dpel.company_name,
                               dper.year_num,
                               dper.MONTH_NUM,
                               dper.month_name,
                               COUNT (DISTINCT a.CONTRACT_NO) Jumlah_Jaringan,
                               djj.network_service_type_1_code Jasa_Level_1,
                               djj.network_service_type_2_code Jasa_Level_2,
                               djj.network_service_type_3_code Jasa_Level_3
                        FROM   dim_period dper,
                               t_fact_saldo_akhir_target a,
                               dim_pelanggan dpel,
                               dim_organisasi_sales dos,
                               dim_network_owner dno,
                               dim_jenis_jasa djj
                       WHERE       a.network_owner_key = dno.network_owner_key
                               AND a.pelanggan_key = dpel.pelanggan_key
                               AND a.period_bulan_data_key = dper.period_key
                               AND a.organisasi_sales_key = dos.organisasi_sales_key
                               --AND dper.year_num = 2011
                               --AND dper.MONTH_NUM = 5
                               AND to_date('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= to_date(dper.DAY_NUM || '\' || dper.MONTH_NUM || '\' || dper.year_num, 'dd\mm\yyyy')
                               AND ADD_MONTHS(to_date('1\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy'), 1) > to_date(dper.DAY_NUM || '\' || dper.MONTH_NUM || '\' || dper.year_num, 'dd\mm\yyyy')
                               AND dpel.COMPANY_ID = :prmID --103893
                               AND a.jenis_jasa_key = djj.jenis_jasa_key
                    GROUP BY   --dper.year_num,
                               dper.month_name,
                               dpel.company_no,
                               dpel.company_name,
                               dper.year_num,
                               dper.MONTH_NUM,
                               djj.network_service_type_1_code,
                               djj.network_service_type_2_code,
                               djj.network_service_type_3_code
                    ORDER BY   dper.year_num DESC, dper.MONTH_NUM DESC
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetJasaLevel3Series(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetJasaLevel3Series(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetJasaLevel3Series(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_JASA_LEVEL3_SERIES,
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.DWLA_LA_DWH);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmStart;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmEnd;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Year;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Year;

                String sxQuery =
                    @"
                        SELECT   DISTINCT djj.network_service_type_3_code Jasa_Level_3
                          FROM   dim_period dper,
                                 t_fact_saldo_akhir_target a,
                                 dim_pelanggan dpel,
                                 dim_organisasi_sales dos,
                                 dim_network_owner dno,
                                 dim_jenis_jasa djj
                         WHERE       a.network_owner_key = dno.network_owner_key
                                 AND a.pelanggan_key = dpel.pelanggan_key
                                 AND a.period_bulan_data_key = dper.period_key
                                 AND a.organisasi_sales_key = dos.organisasi_sales_key
                                 AND TO_DATE ('1\' || :prmStartMonth || '\' || :prmStartYear,
                                              'dd\mm\yyyy') <=
                                       TO_DATE (
                                             dper.DAY_NUM
                                          || '\'
                                          || dper.MONTH_NUM
                                          || '\'
                                          || dper.year_num,
                                          'dd\mm\yyyy'
                                       )
                                 AND ADD_MONTHS (
                                       TO_DATE ('1\' || :prmEndMonth || '\' || :prmEndYear,
                                                'dd\mm\yyyy'),
                                       1
                                    ) >
                                       TO_DATE (
                                             dper.DAY_NUM
                                          || '\'
                                          || dper.MONTH_NUM
                                          || '\'
                                          || dper.year_num,
                                          'dd\mm\yyyy'
                                       )
                                 AND dpel.COMPANY_ID = :prmID                                 --103893
                                 AND a.jenis_jasa_key = djj.jenis_jasa_key
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);

            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }        

		public DataTable GetJasaLevel3FilterSeries(int iCompanyId)
        {
            return GetJasaLevel3FilterSeries(iCompanyId, false);
        }
        public DataTable GetJasaLevel3FilterSeries(int iCompanyId, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}",
                KCache.V_REPORT_JASA_LEVEL3_FILTER_SERIES,
                iCompanyId.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.DWLA_LA_DWH);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;
                String sxQuery =
                    @"
                        SELECT   DISTINCT djj.network_service_type_3_code Jasa_Level_3
                          FROM   dim_period dper,
                                 t_fact_saldo_akhir_target a,
                                 dim_pelanggan dpel,
                                 dim_organisasi_sales dos,
                                 dim_network_owner dno,
                                 dim_jenis_jasa djj
                         WHERE       a.network_owner_key = dno.network_owner_key
                                 AND a.pelanggan_key = dpel.pelanggan_key
                                 AND a.period_bulan_data_key = dper.period_key
                                 AND a.organisasi_sales_key = dos.organisasi_sales_key
                                 AND a.jenis_jasa_key = djj.jenis_jasa_key
                                 AND dpel.COMPANY_ID = :prmID 
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);

            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }        

        public override String GetCacheFilename()
        {
            return KCache.Filename.COMPANY_REPORT_VIEWS;
        }
    }
}